﻿/****************************** Module Header ******************************\
 * Module Name:  Sql.cs
 * Module Version:  2.0.0 (2012-8-24)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to make a safely sql command.
 * 
 * 很强大的辅助类，为DBHelper的重构提供了强大的信心哈！
 * #log
 * 2012-8-24 created!
 * 2013-2-16 v2.0.0
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Linq;

namespace UCL.Data
{
    /// <summary>
    /// Sql参数化语句构造类
    /// </summary>
    public class Sql
    {
        private static Sql instance;
        private string sqltext;
        private IDatabaseHelper dbh;
        private TemplateTextHelper tth;
        private List<DbParameter> Parameters;
        private List<string> specParams;
        Dictionary<string, string> dictFields;
        private Sql(string sql)
        {
            dbh = new SQLServerHelper("");
            sqltext = sql;
            tth = new TemplateTextHelper(sql, @"@\d+");
            dictFields = new Dictionary<string, string>();
            Parameters = new List<DbParameter>();
            specParams = new List<string>();
        }

        /// <summary>
        /// 初始化一个新的Sql实例对象
        /// <para>如：Sql.New("select * from tUserMember where (LoginID=@0 or Name like '%@3%') and Sex=@2 and Birthday=@1").AddParam&lt;string&gt;("@0", "sutro'on").AddParam&lt;int&gt;("@2", 0).AddParam&lt;DateTime&gt;("@1", DateTime.Now).AddParam&lt;string&gt;("@3","su'troon",false).ToString();</para>
        /// <para>或Sql.New("select _*0,b.Name as CatalogName from _t0 a left join _t1 b on a.CatalogID=b.ID where a.CatalogID=@0").AddFields&lt;tGeneralArticle&gt;("*0","a").AddTableName&lt;tGeneralArticle&gt;("t0").AddTableName&lt;tGeneralArticle&gt;("t1").AddParam&lt;Guid&gt;("@0",Guid.Empty).ToString();</para>
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static Sql New(string s)
        {
            instance = new Sql(s);
            return instance;
        }

        /// <summary>
        /// 初始化一个新的Sql实例对象
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <param name="whereSql"></param>
        /// <param name="orderBy"></param>
        /// <param name="onSql"></param>
        /// <param name="fields"></param>
        /// <param name="filter"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static Sql LeftJoin<T1, T2>(string whereSql = "", string orderBy = "a.CreatedTime desc", string onSql = "a.CatalogID=b.ID", string fields = "_*0, b.Name as CatalogName", string[] filter = null, int count = 0)
        {
            if (whereSql.Length > 0 && whereSql.GetFirstWord().ToLower() != "where") whereSql = string.Format(" where {0} ", whereSql);
            if (orderBy.Length > 0) orderBy = string.Format(" order by {0} ", orderBy);
            string topN = count == 0 ? string.Empty : string.Format(" top {0} ", count);
            instance = new Sql(string.Format("select {4}{3} from _t0 a left join _t1 b on {0}{1}{2}", onSql, whereSql, orderBy, fields, topN));
            instance.AddTableName<T1>("t0").AddTableName<T2>("t1").AddFields<T1>("*0", "a", filter);
            return instance;
        }

        /// <summary>
        /// 添加强类型参数
        /// <para>如：Sql.New("update tDemo set Title=@0").AddParam&lt;string&gt;("@0",title);</para>
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="autoWrap">是否自动加引号</param>
        /// <returns></returns>
        public Sql AddParam<V>(string name, V value, bool autoWrap = true)
        {
            if (value == null) return instance;
            DbType dtype = dbh.ClrTypeToDbType(typeof(V));
            DbParameter p = dbh.GetDbCommand().CreateParameter();
            p.ParameterName = name;
            p.DbType = dtype;
            p.Direction = ParameterDirection.Input;
            p.Value = value;
            instance.Parameters.Add(p);
            if (autoWrap == false) specParams.Add(name);
            return instance;
        }

        /// <summary>
        /// 替换字段占位符
        /// <para>字段占位符如：_*0,_*a等</para>
        /// <para>如：Sql.New("select _*0,b.Name as CatalogName from _t0 a left join _t1 b on a.CatalogID=b.ID where a.CatalogID=@0").AddFields&lt;tGeneralArticle&gt;("*0","a").ToString();</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="p"></param>
        /// <param name="alias"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public Sql AddFields<T>(string p, string alias = "", string[] filter = null)
        {
            IList<string> fields = ORM.EntityHelper.GetListOfFieldNameByType<T>();
            if (filter != null) fields = fields.Except(filter).ToList();
            dictFields[string.Format("_{0}", p)] = fields.ToString(",", alias.Length == 0 ? "{0}" : string.Format("{0}.{{0}}", alias));
            return instance;
        }

        /// <summary>
        /// 替换表名占位符
        /// <para>表名占位符如：_t0,_ta等</para>
        /// <para>如：Sql.New("select * from _t0 a left join _t1 b on a.CatalogID=b.ID where a.CatalogID=@0").AddTableName&lt;tGeneralArticle&gt;("t0").AddTableName&lt;tGeneralArticle&gt;("t1").ToString();</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="p"></param>
        /// <returns></returns>
        public Sql AddTableName<T>(string p)
        {
            dictFields[string.Format("_{0}", p)] = typeof(T).Name;
            return instance;
        }

        /// <summary>
        /// 输出合成语句
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            string v;
            foreach (DbParameter p in instance.Parameters)
            {
                if (p.DbType.IsType(DbType.String, DbType.Guid, DbType.DateTime, DbType.Binary, DbType.Xml))
                {
                    v = string.Format(specParams.Contains(p.ParameterName) ? "{0}" : "'{0}'", p.Value.ToString().Replace("'", "''"));
                }
                else
                {
                    v = string.Format("{0}", p.Value);
                }
                if (tth.HasVar(p.ParameterName)) tth.SetValue(v);
            }
            instance = null;
            string tpl = tth.FormatText;
            foreach (string s in dictFields.Keys)
            {
                tpl = tpl.Replace(s, dictFields[s]);
            }
            tth.FormatText = tpl;
            return tth.ToString();
        }
    }
}
